package com.wu.dao.impl;

import com.wu.bean.Journal;
import com.wu.dao.JournalDao;
import com.wu.model.JournalModel;

import java.util.List;

public class JournalDaoImpl extends BaseDao implements JournalDao {
    @Override
    public int saveJournal(Journal journal) {
        String sql="INSERT INTO `blogwu`.`journal` (`title`,category, `content`, `author`, `time`,hits,commentolume) VALUES (?,?,?, ?, ?,0,0);";
        return update(sql,journal.getTitle(),journal.getCategory(),journal.getContent(),journal.getAuthor(),journal.getTime());
    }

    @Override
    public List<JournalModel> queryJournalAll() {
        // String sql="SELECT id, `title`, `content`, `author`, `time` FROM journal";
        String sql="SELECT j.id,j.title,j.content,u.username,j.time,c.categoryname,j.hits,j.commentolume FROM journal j,USER u,categorytable c WHERE j.author=u.id AND j.category=c.id";
        return queryForList(JournalModel.class,sql);
    }

    @Override
    public int updateJournal(Journal journal) {
        String sql="UPDATE journal SET category=?, title=?,content=? WHERE id=?";
        return update(sql,journal.getCategory(),journal.getTitle(),journal.getContent(),journal.getId());
    }

    @Override
    public JournalModel queryJournalID(int id) {
        String sql="SELECT j.id,j.title,j.content,u.username,j.time,c.categoryname,j.hits,j.commentolume FROM journal j,USER u,categorytable c WHERE j.author=u.id AND j.category=c.id AND j.id=?";
        return queryForOne(JournalModel.class,sql,id);
    }


    @Override
    public int deleteJournal(int id) {
        String sql="DELETE FROM journal WHERE id=?";
        return update(sql,id);
    }
    @Override
    public List<JournalModel> queryJournalLike(String like) {
        //String sql="SELECT id, `title`, `content`, `author`, `time` FROM journal WHERE title LIKE ? OR content LIKE ?";
        String sql="SELECT j.id,j.title,j.content,u.username,j.time,c.categoryname,j.hits,j.commentolume FROM journal j,USER u,categorytable c WHERE j.author=u.id AND j.category=c.id AND (j.title LIKE ? OR j.content LIKE ?)";
        return queryForList(JournalModel.class,sql,"%"+like+"%","%"+like+"%");
    }

    @Override
    public List<JournalModel> queryJournalLimitTen() {
        String sql="SELECT j.id,j.title,u.username,j.time,c.categoryname,j.hits,j.commentolume,j.content \n" +
                "FROM journal j,USER u,categorytable c WHERE j.author=u.id AND j.category=c.id\n" +
                "ORDER BY j.time DESC";
        return queryForList(JournalModel.class,sql);
    }

    @Override
    public List<JournalModel> queryJournalCommentolumeFive() {
        String sql="SELECT j.id,j.title,j.commentolume FROM journal j ORDER BY j.commentolume DESC LIMIT 5";
        return queryForList(JournalModel.class,sql);
    }

    @Override
    public List<JournalModel> queryJournalHitsFive() {
       String sql="SELECT j.id,j.title,j.hits FROM journal j ORDER BY j.hits DESC LIMIT 5";
        return queryForList(JournalModel.class,sql);
    }

    @Override
    public int updateHits(int id,int hits) {
        String sql="UPDATE journal SET hits=? WHERE id=?";
        return update(sql,hits,id);
    }

    @Override
    public int updateCommentolume(int id,int Commentolume) {
        String sql="UPDATE journal SET commentolume=? WHERE id=?";
        return update(sql,Commentolume,id);
    }

    @Override
    public JournalModel queryJournalTopOne(int id) {
        String sql="SELECT j.id,j.title,j.content,u.username,j.time,c.categoryname,j.hits,j.commentolume FROM journal j,USER u,categorytable c WHERE j.author=u.id AND j.category=c.id AND j.id < ? ORDER BY j.id DESC  LIMIT 0,1;";
        return queryForOne(JournalModel.class,sql,id);
    }

    @Override
    public JournalModel queryJournalBunOne(int id) {
        String sql="SELECT j.id,j.title,j.content,u.username,j.time,c.categoryname,j.hits,j.commentolume FROM journal j,USER u,categorytable c WHERE j.author=u.id AND j.category=c.id AND  j.id > ? ORDER BY j.id   LIMIT 0,1;  ";
        return queryForOne(JournalModel.class,sql,id);
    }
}
